package com.jdd.modules.parkmonthly.util;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.*;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelReader {

	/**
	 * Excel读取 操作
	 */
	public static List<List<String>> readExcel(InputStream is)
			throws IOException {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		/** 得到第一个sheet */
		Sheet sheet = wb.getSheetAt(0);
		/** 得到Excel的行数 */
		int totalRows = sheet.getPhysicalNumberOfRows();

		//获得当前sheet的开始行
        int firstRowNum  = sheet.getFirstRowNum();
         //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();

		/** 得到Excel的列数 */
		int totalCells = 0;
		if (totalRows >= 1 && sheet.getRow(0) != null) {
			totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}

		List<List<String>> dataLst = new ArrayList<List<String>>();
		/** 循环Excel的行 */
		for (int r = firstRowNum; r <= lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {

                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    cellValue = hSSFDataFormatter.formatCellValue(cell);
                    // 以下是判断数据的类型
					/*
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: // 数字
                        cellValue = cell.getNumericCellValue() + "";
                        break;
                    case Cell.CELL_TYPE_STRING: // 字符串
                        cellValue = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN: // Boolean
                        cellValue = cell.getBooleanCellValue() + "";
                        break;
                    case Cell.CELL_TYPE_FORMULA: // 公式
                        cellValue = cell.getCellFormula() + "";
                        break;
                    case Cell.CELL_TYPE_BLANK: // 空值
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_ERROR: // 故障
                        cellValue = "非法字符";
                        break;
                    default:
                        cellValue = "未知类型";
                        break;
                    }*/

                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    /**
     * Excel读取 操作扩展类
     */
	/*public static List<List<String>> readExcelExt(InputStream is)
			throws IOException {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		*//** 得到第一个sheet *//*
		Sheet sheet = wb.getSheetAt(0);
		*//** 得到Excel的行数 *//*
		int totalRows = sheet.getPhysicalNumberOfRows();
		//获得当前sheet的开始行
        int firstRowNum  = sheet.getFirstRowNum();
         //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();

		*//** 得到Excel的列数 *//*
		int totalCells = 0;
		if (totalRows >= 1 && sheet.getRow(0) != null) {
			totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}

		List<List<String>> dataLst = new ArrayList<List<String>>();
		*//** 循环Excel的行 *//*
		for (int r = firstRowNum; r <= lastRowNum; r++) {
			Row row = sheet.getRow(r);
			if (row == null)
				continue;
			List<String> rowLst = new ArrayList<String>();
			*//** 循环Excel的列 *//*
			for (int c = 0; c < totalCells; c++) {
				Cell cell = row.getCell(c);
				String cellValue = "";
				if (null != cell) {

					HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
					cellValue = hSSFDataFormatter.formatCellValue(cell);
					// 以下是判断数据的类型

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: // 数字
                    	String dataFormat = cell.getCellStyle().getDataFormatString();	// 单元格格式
                    	if("yyyy/mm;@".equals(dataFormat) || "m/d/yy".equals(dataFormat)
                    	        || "yy/m/d".equals(dataFormat) || "mm/dd/yy".equals(dataFormat)
                    	        || "dd-mmm-yy".equals(dataFormat)|| "yyyy/m/d".equals(dataFormat)){
                    		cellValue = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
                    	}
                        break;
                    case Cell.CELL_TYPE_STRING: // 字符串
                        cellValue = cell.getStringCellValue();
                        break;
                    }

				}
				rowLst.add(cellValue);
			}
			*//** 保存第r行的第c列 *//*
			dataLst.add(rowLst);
		}
		return dataLst;
	}*/
}
